# Loading required packages
library(stargazer);library(miceadds)

# Loading the data
load("county_level_allocation_data.RData")

###########
# TABLE 1 #
###########

# Looking at one request for each subcommittee
# Agriculture
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Feinstein" & senators_appropriations_matched$subcommittee == "Agriculture, Rural Development, Food and Drug Administration, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "City of Huron"),"purpose"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Feinstein" & senators_appropriations_matched$subcommittee == "Agriculture, Rural Development, Food and Drug Administration, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "City of Huron"),"amount"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Feinstein" & senators_appropriations_matched$subcommittee == "Agriculture, Rural Development, Food and Drug Administration, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "City of Huron"),"percent_request_fulfilled"]

# Commerce
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Hyde-Smith" & senators_appropriations_matched$subcommittee == "Commerce, Justice, Science, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "University of Southern Mississippi"),"purpose"][1,]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Hyde-Smith" & senators_appropriations_matched$subcommittee == "Commerce, Justice, Science, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "University of Southern Mississippi"),"amount"][1,]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Hyde-Smith" & senators_appropriations_matched$subcommittee == "Commerce, Justice, Science, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "University of Southern Mississippi"),"percent_request_fulfilled"][1,]

# Energy
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Murkowski" & senators_appropriations_matched$subcommittee == "Energy and Water Development" & 
                     senators_appropriations_matched$recipient == "Renewable Juneau"),"purpose"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Murkowski" & senators_appropriations_matched$subcommittee == "Energy and Water Development" & 
                     senators_appropriations_matched$recipient == "Renewable Juneau"),"amount"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Murkowski" & senators_appropriations_matched$subcommittee == "Energy and Water Development" & 
                     senators_appropriations_matched$recipient == "Renewable Juneau"),"percent_request_fulfilled"]

# Financial Services
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Heinrich" & senators_appropriations_matched$subcommittee == "Financial Services and General Government" & 
                     senators_appropriations_matched$recipient == "University of New Mexico"),"purpose"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Heinrich" & senators_appropriations_matched$subcommittee == "Financial Services and General Government" & 
                     senators_appropriations_matched$recipient == "University of New Mexico"),"amount"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Heinrich" & senators_appropriations_matched$subcommittee == "Financial Services and General Government" & 
                     senators_appropriations_matched$recipient == "University of New Mexico"),"percent_request_fulfilled"]

# Homeland
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Booker" & senators_appropriations_matched$subcommittee == "Homeland Security" & 
                     senators_appropriations_matched$recipient == "New Jersey Coastal Coalition"),"purpose"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Booker" & senators_appropriations_matched$subcommittee == "Homeland Security" & 
                     senators_appropriations_matched$recipient == "New Jersey Coastal Coalition"),"amount"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Booker" & senators_appropriations_matched$subcommittee == "Homeland Security" & 
                     senators_appropriations_matched$recipient == "New Jersey Coastal Coalition"),"percent_request_fulfilled"]

# Interior
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Capito" & senators_appropriations_matched$subcommittee == "Interior, Environment, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "Alpine Heritage Preservation"),"purpose"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Capito" & senators_appropriations_matched$subcommittee == "Interior, Environment, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "Alpine Heritage Preservation"),"amount"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Capito" & senators_appropriations_matched$subcommittee == "Interior, Environment, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "Alpine Heritage Preservation"),"percent_request_fulfilled"]

# Labor
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Peters" & senators_appropriations_matched$subcommittee == "Labor, Health and Human Services, Education, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "Monroe County Community College"),"purpose"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Peters" & senators_appropriations_matched$subcommittee == "Labor, Health and Human Services, Education, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "Monroe County Community College"),"amount"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Peters" & senators_appropriations_matched$subcommittee == "Labor, Health and Human Services, Education, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "Monroe County Community College"),"percent_request_fulfilled"]

# Military
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Brown" & senators_appropriations_matched$subcommittee == "Military Construction, Veterans Affairs, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "Rickenbacker Air National Guard Base"),"purpose"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Brown" & senators_appropriations_matched$subcommittee == "Military Construction, Veterans Affairs, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "Rickenbacker Air National Guard Base"),"amount"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Brown" & senators_appropriations_matched$subcommittee == "Military Construction, Veterans Affairs, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "Rickenbacker Air National Guard Base"),"percent_request_fulfilled"]

# Transportation
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Boozman" & senators_appropriations_matched$subcommittee == "Transportation, Housing and Urban Development, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "Northwest Arkansas Regional Airport Authority"),"purpose"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Boozman" & senators_appropriations_matched$subcommittee == "Transportation, Housing and Urban Development, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "Northwest Arkansas Regional Airport Authority"),"amount"]
senators_appropriations_matched[which(senators_appropriations_matched$senator == "Senator Boozman" & senators_appropriations_matched$subcommittee == "Transportation, Housing and Urban Development, and Related Agencies" & 
                     senators_appropriations_matched$recipient == "Northwest Arkansas Regional Airport Authority"),"percent_request_fulfilled"]


###########
# TABLE 3 #
###########
# Limit to the observations that have all variables 
senators_appropriations_matched <- senators_appropriations_matched[!is.na(senators_appropriations_matched$mean_core_county),]

# Descriptives on how many fulfilled at greater than 100%: About 1.4 percent of requests are fulfilled at over 100%
round(length(which(senators_appropriations_matched$percent_request_fulfilled>1))/length(senators_appropriations_matched$percent_request_fulfilled),3)

# Range of 0 to 28.8, mean of 0.3
summary(senators_appropriations_matched$percent_request_fulfilled)

# 6950 with at least partial fulfillment
length(which(senators_appropriations_matched$percent_request_fulfilled>0))

# Model 1: Outcome variable is binary (was request granted or not)
reg1_fe <- lm.cluster(request_granted ~ amount_percapita_scaled + log.total.requests.made_scaled +  joint_request + num_counties_scaled +
                        dem + on_appropriations + meddist_scaled + mean_core_county_scaled + mean_swing_county_scaled +
                        logpop_scaled + female + seniority_scaled + party_leader +
                        freshman + mean_pct_urban_scaled + mean_median_household_income_scaled + any_capital + factor(year) + factor(subcommittee),
                      cluster = "senator", data=senators_appropriations_matched)
summary(reg1_fe)

reg1 <- lm(request_granted ~ amount_percapita_scaled + log.total.requests.made_scaled + joint_request + num_counties_scaled +
             dem + on_appropriations + meddist_scaled + mean_core_county_scaled + mean_swing_county_scaled +
             logpop_scaled + female + seniority_scaled + party_leader +
             freshman + mean_pct_urban_scaled + mean_median_household_income_scaled + any_capital +  factor(year) + factor(subcommittee),data=senators_appropriations_matched)
summary(reg1)

# Model 2: Outcome variable is a percentage (percentage of request that was fulfilled)

reg2_fe <- lm.cluster(percent_request_fulfilled ~ amount_percapita_scaled + log.total.requests.made_scaled + joint_request + num_counties_scaled +
                        dem + on_appropriations + meddist_scaled + mean_core_county_scaled + mean_swing_county_scaled +
                        logpop_scaled + female + seniority_scaled + party_leader +
                        freshman + mean_pct_urban_scaled + mean_median_household_income_scaled + any_capital +  factor(year) + factor(subcommittee),
                      cluster = "senator", data=senators_appropriations_matched)
summary(reg2_fe)

reg2 <- lm(percent_request_fulfilled ~ amount_percapita_scaled + log.total.requests.made_scaled + joint_request + num_counties_scaled +
             dem + on_appropriations + meddist_scaled + mean_core_county_scaled + mean_swing_county_scaled +
             logpop_scaled + female + seniority_scaled + party_leader +
             freshman + mean_pct_urban_scaled + mean_median_household_income_scaled + any_capital +  factor(year) + factor(subcommittee),data=senators_appropriations_matched)

summary(reg2)

# Storing coefficients
cond  <- data.frame(summary(reg1_fe))
cond2  <- data.frame(summary(reg2_fe))

coefs <- c(cond[,1])
ses <- c(cond[,2])
coefs2 <- c(cond2[,1])
ses2 <- c(cond2[,2])

# Stargazer takes the non-clustered regressions, and we feed it the clustered standard errors
stargazer(reg1, reg2,
          add.lines=list(c("Subcommittee Fixed Effects","\\checkmark","\\checkmark"),
                         c("Year Fixed Effects","\\checkmark","\\checkmark")),
           omit=c("Constant", "subcommittee", "year"),
           notes.append = FALSE,notes.label = "",
           report="vc*s",star.char=c("*","**"),star.cutoffs = c(0.10,0.05),no.space = TRUE,
           font.size = "footnotesize", model.numbers = FALSE,
           dep.var.labels = c("Request Granted (Binary)","Percent of Request Granted"),column.sep.width="0pt",
           covariate.labels=c( "Request Amount (Per Capita)", "Total Amount of Senator Requests (Log)", "Joint Request", "Number of Counties Affected", "Democrat (Majority Party Member)",
                            "Member of Appropriations Committee","Distance from DW-NOMINATE Median", "Percent Core Counties", "Percent Swing Counties",
                             "Log Total Population in Affected Counties", "Senator is a Woman","Seniority", "Party Leader", 
                             "Freshman Senator", "Average Percent Urban Population in Affected Counties", "Average Median Household Income in Affected Counties", "Capital City"),
           notes="\\parbox[t]{\\textwidth}{\\footnotesize \\textit{Note}: The table shows the results from OLS regressions of a
           senator's county-level appropriation request behavior. Note that these models are limited to senators who actually made a request. Standard errors are clustered by senator as senators make multiple requests. $^{**}p<0.05$, $^*p<0.10$. All continuous variables are standardized for ease of comparison.}",
           label="tab1_county_matched",
           digits=3,
           coef=list(coefs, coefs2), 
           se=list(ses, ses2),
           digits.extra = 0,
           title="Predictors of Approved Spending Requests",
           omit.stat = c("ll","rsq","adj.rsq","ser","f"))
